﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using FU.Capstones.IMS.Services.Common;
using FU.Capstones.IMS.Services.Common.DataContracts;
using FU.Capstones.IMS.Services.Common.Respondents;
using FU.Capstones.IMS.Services.DataContracts;
using FU.Capstones.IMS.Services.Interfaces;

namespace FU.Capstones.IMS.Services.DAOs
{
    internal class MaterialDAO : DAOBase, IMaterialDAO
    {
        #region IMaterialDAO Members

        public CollectionFeedback<Material> GetCollection(bool isShowAll)
        {
            try
            {
                var command = new SqlCommand("GetCollectionMaterial", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@IsShowAll", isShowAll);

                var dt = new DataTable("AllMaterial");
                var sqlda = new SqlDataAdapter(command);
                sqlda.Fill(dt);
                var list = new List<Material>();
                if (dt.Rows.Count != 0)
                {
                    foreach (DataRow dataRow in dt.Rows)
                    {
                        var warehouse = new Warehouse();
                        if (dataRow["WarehouseID"] != DBNull.Value)
                        {
                            warehouse.WarehouseID = Convert.ToInt32(dataRow["WarehouseID"]);
                            warehouse.WarehouseName = Convert.ToString(dataRow["WarehouseName"]);
                        }
                        var result = new Material();
                        result.Warehouse = warehouse;
                        result.MaterialID = Convert.ToInt32(dataRow["MaterialID"]);
                        result.MaterialName = Convert.ToString(dataRow["MaterialName"]);
                        result.MaterialCode = Convert.ToString(dataRow["MaterialCode"]);
                        if (dataRow["Quantity"] != DBNull.Value)
                        {
                            result.Quantity = Convert.ToInt32(dataRow["Quantity"]);
                        }
                        else
                        {
                            result.Quantity = 0;
                        }
                        if (dataRow["Description"] != DBNull.Value)
                        {
                            result.Description = Convert.ToString(dataRow["Description"]);
                        }
                        result.IsDelete = Convert.ToBoolean(dataRow["IsDelete"]);
                        list.Add(result);
                    }
                }
                return new CollectionFeedback<Material>(InventoryConstants.Messages.GetSuccessfully, true, list);
            }
            catch (SqlException e)
            {
                return new CollectionFeedback<Material>(InventoryConstants.Messages.GetFailed + e.Message, false, null);
            }
            catch (Exception e)
            {
                return new CollectionFeedback<Material>(InventoryConstants.Messages.GetFailed + e.Message, false, null);
            }
            finally
            {
                CloseConnection();
            }
        }

        public CollectionFeedback<Material> GetCollectionType(bool isShowAll)
        {
            try
            {
                var command = new SqlCommand("GetCollectionMaterialType", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@IsShowAll", isShowAll);

                var dt = new DataTable("AllMaterial");
                var sqlda = new SqlDataAdapter(command);
                sqlda.Fill(dt);
                var list = new List<Material>();
                if (dt.Rows.Count != 0)
                {
                    foreach (DataRow dataRow in dt.Rows)
                    {
                        var result = new Material();
                        result.MaterialID = Convert.ToInt32(dataRow["MaterialID"]);
                        result.MaterialCode = Convert.ToString(dataRow["MaterialCode"]);
                        result.MaterialName = Convert.ToString(dataRow["MaterialName"]);
                        if (dataRow["Description"] != DBNull.Value)
                        {
                            result.Description = Convert.ToString(dataRow["Description"]);
                        }
                        result.IsDelete = Convert.ToBoolean(dataRow["IsDelete"]);
                        list.Add(result);
                    }
                }
                return new CollectionFeedback<Material>(InventoryConstants.Messages.GetSuccessfully, true, list);
            }
            catch (SqlException e)
            {
                return new CollectionFeedback<Material>(InventoryConstants.Messages.GetFailed + e.Message, false, null);
            }
            catch (Exception e)
            {
                return new CollectionFeedback<Material>(InventoryConstants.Messages.GetFailed + e.Message, false, null);
            }
            finally
            {
                CloseConnection();
            }
        }

        public SingleFeedback<Material> GetSingle(int objectID)
        {
            try
            {
                var command = new SqlCommand("GetSingleMaterial", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("MaterialID", objectID);

                //create a data table to hold all retured table
                var dataTable = new DataTable("SingleMaterial");

                //create a sqlDataAdapter to fill data into datatalbe
                var adapter = new SqlDataAdapter(command);
                adapter.Fill(dataTable);

                if (dataTable.Rows.Count != 0)
                {
                    DataRow dataRow = dataTable.Rows[0];
                    var result = new Material();
                    result.MaterialID = Convert.ToInt32(dataRow["MaterialID"]);
                    result.MaterialCode = Convert.ToString(dataRow["MaterialCode"]);
                    result.MaterialName = Convert.ToString(dataRow["MaterialName"]);
                    if (dataRow["Description"] != DBNull.Value)
                    {
                        result.Description = Convert.ToString(dataRow["Description"]);
                    }
                    result.IsDelete = Convert.ToBoolean(dataRow["IsDelete"]);
                    return new SingleFeedback<Material>(InventoryConstants.Messages.GetSuccessfully, true, result);
                }
                else
                {
                    return new SingleFeedback<Material>(InventoryConstants.Messages.GetFailed, false, null);
                }
                //OpenConnection();
                //SqlDataReader reader =  command.ExecuteReader();
            }
            catch (Exception e)
            {
                Log.Error(e.Message);
                return new SingleFeedback<Material>(InventoryConstants.Messages.GetFailed, false, null);
            }
            finally
            {
                CloseConnection();
            }
        }

        public SingleFeedback<int> Insert(Material data)
        {
            var feedback = new SingleFeedback<int>();
            try
            {
                var command = new SqlCommand("InsertMaterial", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@MaterialCode", data.MaterialCode);
                command.Parameters.AddWithValue("@MaterialName", data.MaterialName);
                command.Parameters.AddWithValue("@Description", data.Description);

                OpenConnection();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        int materialID = Convert.ToInt32(reader["MaterialID"]);

                        feedback.Data = materialID;
                        feedback.Message = InventoryConstants.Messages.InsertSuccessfully;
                        feedback.IsSuccessful = true;

                        break;
                    }
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.InsertFailed;
                    feedback.IsSuccessful = false;
                    feedback.Data = -1;
                }

                Log.Info("InsertMaterial: {0} - {1}", feedback.Data, DateTime.Now);
            }
            catch (SqlException se)
            {
                Log.Error("InsertMaterial - SqlException: {0} - {1}", se.StackTrace, DateTime.Now);
                feedback.Message = se.Message;
            }
            catch (Exception e)
            {
                Log.Error("InsertMaterial - Exception: {0} - {1}", e.StackTrace, DateTime.Now);
                feedback.Message = e.Message;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public Feedback Update(Material data)
        {
            var feedback = new Feedback();
            try
            {
                //create a new sqlcommand 
                var command = new SqlCommand("UpdateMaterial", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                //
                command.Parameters.AddWithValue("@MaterialID", data.MaterialID);
                command.Parameters.AddWithValue("@MaterialCode", data.MaterialCode);
                command.Parameters.AddWithValue("@MaterialName", data.MaterialName);
                command.Parameters.AddWithValue("@Description", data.Description);

                OpenConnection();

                int rowCount = command.ExecuteNonQuery();
                if (rowCount == 1)
                {
                    feedback.Message = InventoryConstants.Messages.UpdateSuccessfully;
                    feedback.IsSuccessful = true;
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.UpdateFailed;
                    feedback.IsSuccessful = false;
                }

                Log.Info("UpdateMaterial: {0} - {1} - {2}", data.MaterialID, feedback.Message, DateTime.Now);
            }
            catch (SqlException se)
            {
                Log.Error("UpdateMaterial - SqlException: {0} - {1} - {2}", data.MaterialID, se.StackTrace, DateTime.Now);
                feedback.Message = se.Message;
            }
            catch (Exception e)
            {
                Log.Error("DeleteMaterial - Exception: {0} - {1} - {2}", data.MaterialID, e.StackTrace, DateTime.Now);
                feedback.Message = e.Message;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public Feedback Delete(int objID)
        {
            var feedback = new Feedback();
            try
            {
                var command = new SqlCommand("DeleteMaterial", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@MaterialID", objID);

                OpenConnection();

                int rowCount = command.ExecuteNonQuery();
                if (rowCount == 1)
                {
                    feedback.Message = InventoryConstants.Messages.DeleteSuccessfully;
                    feedback.IsSuccessful = true;
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.DeleteFailed;
                    feedback.IsSuccessful = false;
                }

                Log.Info("DeleteMaterial: {0} - {1} - {2}", objID, feedback.Message, DateTime.Now);
            }
            catch (SqlException se)
            {
                Log.Error("DeleteMaterial - SqlException: {0} - {1} - {2}", objID, se.StackTrace, DateTime.Now);
                feedback.Message = se.Message;
            }
            catch (Exception e)
            {
                Log.Error("DeleteMaterial - Exception: {0} - {1} - {2}", objID, e.StackTrace, DateTime.Now);
                feedback.Message = e.Message;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public CollectionFeedback<Material> Search(Material criteria)
        {
            var feedback = new CollectionFeedback<Material> {IsSuccessful = false, Data = new List<Material>()};
            var mCriteria = (MaterialCriteria) criteria;

            try
            {
                OpenConnection();

                var command = new SqlCommand("SearchMaterial", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@MaterialCode",
                                                String.IsNullOrEmpty(mCriteria.MaterialCode)
                                                    ? mCriteria.MaterialCode
                                                    : mCriteria.MaterialCode.Trim());
                command.Parameters.AddWithValue("@MaterialName",
                                                String.IsNullOrEmpty(mCriteria.MaterialName)
                                                    ? mCriteria.MaterialName
                                                    : mCriteria.MaterialName.Trim());
                command.Parameters.AddWithValue("@TotalQuantityFrom", mCriteria.TotalQuantityFrom);
                command.Parameters.AddWithValue("@TotalQuantityTo", mCriteria.TotalQuantityTo);
                if (mCriteria.WarehouseIDCollection != null && mCriteria.WarehouseIDCollection.Count > 0)
                {
                    command.Parameters.AddWithValue("@WarehouseID", mCriteria.WarehouseIDCollection[0]);
                }


                SqlDataReader reader = command.ExecuteReader();

                var materials = new List<Material>();

                while (reader.Read())
                {
                    var warehouse = new Warehouse();
                    warehouse.WarehouseID = Convert.ToInt32(reader["WarehouseID"]);
                    warehouse.WarehouseName = Convert.ToString(reader["WarehouseName"]);

                    var material = new Material();
                    material.WarehouseId = warehouse.WarehouseID;
                    material.Warehouse = warehouse;
                    material.MaterialID = Convert.ToInt32(reader["MaterialID"]);
                    material.MaterialCode = Convert.ToString(reader["MaterialCode"]);
                    material.MaterialName = Convert.ToString(reader["MaterialName"]);
                    material.Quantity = Convert.ToInt32(reader["Quantity"]);
                    material.Description = Convert.ToString(reader["Description"]);
                    material.IsDelete = Convert.ToBoolean(reader["IsDelete"]);

                    materials.Add(material);
                }

                feedback.Message = InventoryConstants.Messages.SearchSuccessfully;
                feedback.IsSuccessful = true;
                feedback.Data = materials;


                Log.Info("SearchMaterial: {0}, {1}", feedback.Message, DateTime.Now.ToString());
            }
            catch (SqlException e)
            {
                Log.Error(e.Message);
                feedback.Message = InventoryConstants.Messages.SearchFailed;
            }
            catch (Exception t)
            {
                Log.Error(t.Message);
                feedback.Message = InventoryConstants.Messages.SearchFailed;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public Feedback UndoDelete(int objID)
        {
            var feedback = new Feedback();
            try
            {
                var command = new SqlCommand("UndoDeletedMaterial", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@MaterialID", objID);

                OpenConnection();

                int rowCount = command.ExecuteNonQuery();
                if (rowCount == 1)
                {
                    feedback.Message = InventoryConstants.Messages.UndoDeleteSuccessfully;
                    feedback.IsSuccessful = true;
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.UndoDeleteFailed;
                    feedback.IsSuccessful = false;
                }

                Log.Info("UndoDeletedMaterial: {0} - {1} - {2}", objID, feedback.Message, DateTime.Now);
            }
            catch (SqlException se)
            {
                Log.Error("UndoDeletedMaterial - SqlException: {0} - {1} - {2}", objID, se.StackTrace, DateTime.Now);
                feedback.Message = se.Message;
            }
            catch (Exception e)
            {
                Log.Error("UndoDeletedMaterial - Exception: {0} - {1} - {2}", objID, e.StackTrace, DateTime.Now);
                feedback.Message = e.Message;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        #endregion
    }
}